package org.jxc.dao;

import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.jxc.domain.SalesOrder;
import org.team4.dao.GenericDAO;
import org.team4.domain.Users;
import org.team4.util.LoginBean;
import org.team4.util.PageHelper;
import org.team4.vo.Page;

public class SalesOrderDAO extends GenericDAO<SalesOrder> {
	private static final long serialVersionUID = 4950197358876315858L;
	private static SalesOrderDAO instance;

	public static SalesOrderDAO getInstance(SessionFactory sessionFactory) {
		if (instance == null) {
			instance = new SalesOrderDAO(sessionFactory);
		}
		return instance;
	}

	private SalesOrderDAO(SessionFactory sessionFactory) {
		super(sessionFactory, SalesOrder.class);
	}

	@SuppressWarnings("unchecked")
	public Page<Object> findSalesCount(Page<Object> page, int year) {
		Users user = LoginBean.getLoginBean().getUser();
		String sql = null;
		if (user.getDepartment().getManager() == user) {
			sql = "select u.accountName"
					+ ",u.realName"
					+ ",sum(case MONTH(t.createTime) when 1 then totalPrice else 0 end) as one"
					+ ",sum(case MONTH(t.createTime) when 2 then totalPrice else 0 end) as two"
					+ ",sum(case MONTH(t.createTime) when 3 then totalPrice else 0 end) as three"
					+ ",sum(case MONTH(t.createTime) when 4 then totalPrice else 0 end) as four"
					+ ",sum(case MONTH(t.createTime) when 5 then totalPrice else 0 end) as five"
					+ ",sum(case MONTH(t.createTime) when 6 then totalPrice else 0 end) as six"
					+ ",sum(case MONTH(t.createTime) when 7 then totalPrice else 0 end) as seven"
					+ ",sum(case MONTH(t.createTime) when 8 then totalPrice else 0 end) as eight"
					+ ",sum(case MONTH(t.createTime) when 9 then totalPrice else 0 end) as night"
					+ ",sum(case MONTH(t.createTime) when 10 then totalPrice else 0 end) as ten"
					+ ",sum(case MONTH(t.createTime) when 11 then totalPrice else 0 end) as eleven"
					+ ",sum(case MONTH(t.createTime) when 12 then totalPrice else 0 end) as twelve"
					+ ",sum(totalPrice) as total "
					+ "FROM [VOA].[dbo].[t_sales_order] t left join users u on t.saler = u.id "
					+ "where YEAR(t.createTime) = " + year
					+ " group by u.accountName,u.realName";
		} else {
			sql = "select u.accountName"
					+ ",u.realName"
					+ ",sum(case MONTH(t.createTime) when 1 then totalPrice else 0 end) as one"
					+ ",sum(case MONTH(t.createTime) when 2 then totalPrice else 0 end) as two"
					+ ",sum(case MONTH(t.createTime) when 3 then totalPrice else 0 end) as three"
					+ ",sum(case MONTH(t.createTime) when 4 then totalPrice else 0 end) as four"
					+ ",sum(case MONTH(t.createTime) when 5 then totalPrice else 0 end) as five"
					+ ",sum(case MONTH(t.createTime) when 6 then totalPrice else 0 end) as six"
					+ ",sum(case MONTH(t.createTime) when 7 then totalPrice else 0 end) as seven"
					+ ",sum(case MONTH(t.createTime) when 8 then totalPrice else 0 end) as eight"
					+ ",sum(case MONTH(t.createTime) when 9 then totalPrice else 0 end) as night"
					+ ",sum(case MONTH(t.createTime) when 10 then totalPrice else 0 end) as ten"
					+ ",sum(case MONTH(t.createTime) when 11 then totalPrice else 0 end) as eleven"
					+ ",sum(case MONTH(t.createTime) when 12 then totalPrice else 0 end) as twelve"
					+ ",sum(totalPrice) as total "
					+ "FROM [VOA].[dbo].[t_sales_order] t left join users u on t.saler = u.id "
					+ "where YEAR(t.createTime) = " + year + " and saler = '"
					+ user.getId() + "' group by u.accountName,u.realName";
		}

		SQLQuery query = getSession().createSQLQuery(sql);

		// 设置分页参数
		query.setFirstResult(PageHelper.getFirstRow(page.getCurrPage(),
				page.getPageSize()));
		query.setMaxResults(page.getPageSize());

		// count效率高
		SQLQuery countQuery = getSession().createSQLQuery(
				"SELECT COUNT(1) from (" + sql + ") temp");
		// 查找总行数
		int rowCount = (Integer) countQuery.uniqueResult();
		// 结果放入分页类
		page.setRowCount(rowCount);
		page.setList(query.list());
		page.setMaxPage(PageHelper.getMaxPage(rowCount, page.getPageSize()));
		// 返回分页类
		return page;
	}
}
